-- @owner: lvlintao666
-- @date: 2024/05/13
-- @testpoint: prior递归查询性能测试
-- @modified by sungang14，2025.20.29，优化预期step2查询计划
--step1:创建间隔分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_prior_0010;
create table t_prior_0010(pid int,id int);
create or replace procedure insert_data(n in int)
as
pid int;
begin
for id in 1..n loop
    pid = id-1;
insert into t_prior_0010 values(pid,id);
if(mod(id,100) = 0)
	   then commit;
end if;
end loop;
end;
/
call insert_data(200);

--step2 查看prior的查询计划;expect:成功
explain analyze select prior id as father_id, id as current_id from t_prior_0010 start with id = 1 connect by pid = prior id;

--step3 清理环境;expect:成功
drop table if exists t_prior_0010 cascade;
drop procedure insert_data;